package ro.sergiu.finder.ui;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;
import javax.swing.table.TableModel;
import ro.sergiu.finder.MetaDataTableModel;
import ro.sergiu.finder.exception.EngineRunException;
import ro.sergiu.finder.filters.FilterField;
import ro.sergiu.finder.filters.Operator;
import ro.sergiu.finder.models.columns.ColumnModel;
import ro.sergiu.finder.models.columns.db.DbColumnModel;
import ro.sergiu.finder.models.finder.FinderModel;
import ro.sergiu.finder.models.finder.db.DbFinderModel;
import ro.sergiu.finder.utils.StringUtils;
import ro.sergiu.finder.utils.Tools;

/**
 *
 * @author Toporjinschi
 * @param <T>
 */
public class RunSQL<T> extends SearchEngineSpecifications {

    private final Connection connection;
    private final boolean logSQL;
    private MetaDataTableModel<T> metaDataTableModel;
    private DbFinderModel finderModel;
    private List<ColumnModel> selectColumns;

    /**
     * Builds an SQL search engine with debugging off;
     *
     * @param connection the {@link Connection} to SQL server
     */
    public RunSQL(Connection connection) {
        this(connection, false);
    }

    /**
     * Builds an SQL searchEngine
     *
     * @param connection the {@link Connection} to SQL server
     * @param logSQL if is true then the SQL statement will be displayed in console
     */
    public RunSQL(Connection connection, boolean logSQL) {
        this.connection = connection;
        this.logSQL = logSQL;
    }

    @Override
    public void search(String searchTerm) throws EngineRunException {
        try {
            PreparedStatement stmt = connection.prepareStatement(getSQL(StringUtils.isNOTBlank(searchTerm)));
            setParameters(stmt);
            if (StringUtils.isNOTBlank(searchTerm)) {
                setSearchTermParameter(stmt, searchTerm);
            }
            if (logSQL) {
                String sql = stmt.toString();
                System.out.println(sql.substring(sql.indexOf(": ") + 2));
            }
            ResultSet rs = stmt.executeQuery();
            metaDataTableModel = new MetaDataTableModel<T>(rs, finderModel);
            rs.close();
            stmt.close();
        } catch (SQLException ex) {
            throw new EngineRunException("A aparut o erroare!", ex);
        }
    }

    private void setSearchTermParameter(PreparedStatement stmt, String searchTerm) throws SQLException {
        int lastIndex = stmt.getParameterMetaData().getParameterCount() - 1;
        String value = (String) searchTerm.trim().toLowerCase();
        List<FinderModel.SearchTermColumn<String>> searchTermColumns = finderModel.getSearchTermColumns();
        for (FinderModel.SearchTermColumn<String> searchTermColumn : searchTermColumns) {
            lastIndex++;
            if (searchTermColumn.getOperator() == Operator.LIKE) {
                stmt.setString(lastIndex, String.format("%%%s%%", value));
            } else {
                stmt.setString(lastIndex, value);
            }
        }
    }

    /**
     * Puts the value of parameters in SQL Statement
     *
     * @param stmt {@link PreparedStatement} which will be fulfilled with parameters
     * @throws SQLException {@link SQLException} when some error occurs in process
     */
    private void setParameters(PreparedStatement stmt) throws SQLException {
        for (FilterField<String, Object> filter : filterFields) {
            Object val = filter.getValue();
            if (val == null || (val.getClass() == String.class && StringUtils.isBlank((String) val))) {
                continue;
            }
            if (filter.getOperator() == Operator.LIKE) {
                val = "%" + String.valueOf(val) + "%";
            }
            Tools.setStatementBasedOnType(stmt, val);
        }
    }

    /**
     * Builds and returns the SQL statement
     *
     * @return a {@link String} with SQL statement
     */
    private String getSQL(boolean withSearchTerm) {
        StringBuilder sql = new StringBuilder();
        sql.append(String.format("SELECT %s FROM %s WHERE 1=1 ", getFields(), finderModel.getTableName()));
        if (this.finderModel.hasExtraFilters()) {
            sql.append(String.format("AND ( %s )", this.finderModel.getExtraFilters()));
        }
        sql.append(getWhereClause());
        if (withSearchTerm) {
            sql.append(getWhereForSearchTerm());
        }
        return sql.toString();
    }

    /**
     * Builds the where clause based on Filter fields and values inputed by user.
     *
     * @return a {@link String} with SQL WHERE clause
     */
    private String getWhereClause() {
        String rez = StringUtils.joinBeanItem(filterFields, " AND ", new StringUtils.Predicator<String, FilterField>() {
            @Override
            public String perform(FilterField parm) {
                FilterField<String, String> field = parm;
                Object val = field.getValue();
                if (val == null || (val.getClass() == String.class && StringUtils.isBlank((String) val))) {
                    return null;
                }
                return String.format("%s %s ?", field.getMappingItem(), field.getOperator().getOperator());
            }
        });
        if (StringUtils.isNOTBlank(rez)) {
            return String.format(" AND ( %s )", rez);
        }
        return "";
    }

    private String getWhereForSearchTerm() {
        List<FinderModel.SearchTermColumn<String>> searchTermColumns = finderModel.getSearchTermColumns();
        String rez = StringUtils.joinBeanItem(searchTermColumns, " OR ", new StringUtils.Predicator<String, FinderModel.SearchTermColumn<String>>() {
            @Override
            public String perform(FinderModel.SearchTermColumn<String> parameter) {
                return String.format(" ( %s %s ? )", parameter.getMappingItem(), parameter.getOperator().getOperator());
            }
        });
        if (StringUtils.isNOTBlank(rez)) {
            return String.format(" AND ( %s )", rez);
        }
        return "";
    }

    /**
     * Builds and returns the list of columns which will be used in building the SQL Statement
     *
     * @return a {@link String} with SQL columns separated by comma.
     */
    private String getFields() {
        StringBuilder sb = new StringBuilder();
        for (ColumnModel<String> col : selectColumns) {
            sb.append(col.getMapItem()).append(", ");
        }
        ColumnModel<String> last = selectColumns.get(selectColumns.size() - 1);
        return sb.append(last.getMapItem()).toString();
    }

    /**
     * Called by the find dialog for getting an table model which contain the results
     *
     * @return {@link TableModel} populated with the search result
     */
    @Override
    public TableModel getTableModel() {
        return metaDataTableModel;
    }

    /**
     * Called by find dialog with the received {@link FinderModel}. In this case is override because
     * I need to cast the model to {@link DbFinderModel}. And we need to extract the column list in
     * {@link DbColumnModel} format.
     *
     * @param model the {@link FinderModel} the finder model
     */
    @Override
    public void setModel(FinderModel model) {
        this.finderModel = (DbFinderModel) model;
        this.selectColumns = this.finderModel.getColumns();
    }

}